const conn = require('../db/config')

exports.all = (req,res) => {
    var sql = 'select * from handlelist'
    var page = req.query.offset || 0
    var limit = req.query.limit || 10
    conn.query(sql,(err,data)=>{
        if(err){
            return res.send('错误')
        }
        var sqllen = 'select num, handleperson, date_format(time,"%Y-%m-%d %H:%i:%s") as time, sum from handlelist LIMIT '+ limit +' offset '+ page 
        conn.query(sqllen,(err,result)=>{
            if(result.length) 
              res.send({
                data:result,
                total:data.length,
                status:200
              })
            else res.send({msg:'获取失败',status:500})
        })
    })
}

exports.search = (req,res) => {
      var datatype = req.query.type
      var data = req.query.search
      var timearr = req.query.timeranges
      if(data!=='') data = "'"+data+"'"
      if(timearr!==''){
          var start = "'"+timearr[0]+"'"
          var end = "'"+timearr[1]+"'"
          var sqllen = "SELECT num,handleperson,date_format(time,'%Y-%m-%d %H:%i:%s') as time,sum FROM `handlelist` WHERE time between "+start+" and "+end
      } 
      var sql = 'SELECT num,handleperson,date_format(time,"%Y-%m-%d %H:%i:%s") as time,sum FROM `handlelist` WHERE '+datatype+' = '+data
      if((datatype===''||data==='')&&timearr!==''){
        conn.query(sqllen,(err,result) => {
            if(err)  return console.log(err)
            if(result.length)     return res.send({data:result,status:200,total:result.length})
            else    return res.send({msg:'未找到',status:500})
        })}
      else if(datatype!==''&&data!==''||timearr==='')
        conn.query(sql,(err,result) => {
            if(err)  return console.log(err)
            if(result.length)    return res.send({data:result,status:200,total:result.length})
            else    return res.send({msg:'未找到',status:500})
        })
      else{
          if(datatype === 'num') 
            conn.query(sql,(err,result) => {
              if(err) return console.log(err)
              if(result.length) {
                  return res.send({data:result,status:200,total:result.length})
              }else{
                  conn.query(sqllen,(err,result) => {
                      if(err)   return console.log(err)
                      if(result.length)    return res.send({data:result,status:200,total:result.length})
                      else    return res.send({msg:'未找到',status:500})
                  })
              }})
          else{
              conn.query(sql,(err,result) => {
                  if(err) return console.log(err)
                  if(result.length) {
                      var sqll = sqllen + " and "+datatype+" = "+data
                      conn.query(sqll,(err,put) => {
                          if(err)   return console.log(err)
                          if(put.length)    return res.send({data:put,status:200,total:put.length})
                          else    return res.send({data:result,status:200})
                      })
                  }
              }) 
          }
      }
}